/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package functions;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import model.Database;
import model.Diagnose;
import model.Doctor;
import model.Dog;
import model.Drug;
import model.Item;
import model.Operation;
import model.Visit;
import stuff.HealingSetComparator;

/**
 *
 * @author JArik
 */
public class Dogs {

    private static PreparedStatement getHealings, getDrugs,getDiagnoses,getOperations;

    public static int killDog(int dogId, Date deathDate) throws SQLException {
        Database db = new Database();
        int res=0;
        if(dogId <=0)
            throw new SQLException("Неправельный идентификатор собаки.");

        res = db.updateDBInfo("dogs",
                              "deathDate= '"+new Date(deathDate.getTime())+"'"
                              ,
                               "id="+dogId);
        db.closeStatement();
        db =null;
        return res;
    }


    public static int deleteDog(int dogId) throws SQLException {
        Database db = new Database();
        int res=0;
        if(dogId <=0)
            throw new SQLException("Неправельный идентификатор собаки.");

        res = db.updateDBInfo("dogs",
                              "deleted=1"
                              ,
                               "id="+dogId);
        db.closeStatement();
        db =null;
        return res;
    }

    public static Dog getById(int id) throws SQLException
    {
        Database db = new Database();
        Dog dog=null;
        ResultSet set = db.getDBInfo("id, userId, age, breed, nick, deathDate", "dogs", "deleted=0");
        if(set.next())
          dog = new Dog(set.getInt(1), set.getDate(3),
                       set.getInt(2), set.getString(4), set.getString(5));
        dog.setDeathDate(set.getDate("deathDate"));
        db.closeStatement();
        db = null;
        return dog;
        
    }
    public static ArrayList<Dog> getByUserId(int id) throws SQLException
    {
        Database db = new Database();
        ResultSet set = db.getDBInfo("id, userId, age, breed, nick, deathDate",
                                     "dogs", "userId = '"+id +"' AND deleted=0 ORDER BY deathDate");
        ArrayList<Dog> dogs = new ArrayList<Dog>();
        while(set.next())
        {
          Dog dog = new  Dog(set.getInt(1), set.getDate(3),
                       set.getInt(2), set.getString(4), set.getString(5));
          dog.setDeathDate(set.getDate("deathDate"));
          
          dogs.add(dog);
        }
        db.closeStatement();
        db =null;
        return dogs;

    }

    public static int addDog(int userId, String nick,String breed,
                             Date date)
                             throws SQLException
    {
        Database db = new Database();
        String what="",values="";
        
        int res=0;
        if(userId <= 0)
           throw new SQLException("Неправельный пользователь.");
        what = "userId";
        values = userId+"";
        if(!nick.isEmpty())
        {
            what +=",nick";
            values +=",'"+nick+"' ";
        }
        if(breed != null && !breed.isEmpty())
        {
            what +=",breed";
            values +=",'"+breed+"' ";
        }
        if(date != null)
        {
            date = new Date(date.getTime());
            what +=",age";
            values +=",'"+date+"'";
        }
        ResultSet set = db.insertDBInfo("dogs", what, values);
        if(set.next())
            res = set.getInt(1);
        db.closeStatement();
        db =null;
        return res;
    }
    public static int changeDog(int dogId, int userId, 
                                 String nick,String breed,Date date, Date dDate)
                             throws SQLException
    {
        Database db = new Database();
        
        int res=0;
        if(dogId <=0)
            throw new SQLException("Неправельный идентификатор собаки.");
        if(userId <= 0)
           throw new SQLException("Неправельный пользователь.");

        String query = "userId="+userId+","+
                       "nick='"+(nick == null ? "":nick)+"',"+
                       "breed='"+(breed == null ? "":breed)+"'";
        if(date != null)
        {
            date = new Date(date.getTime());
               query += ",age='"+date+"'";
        }else
            query += ",age=NULL";
        if(dDate != null)
        {
            date = new Date(dDate.getTime());
            query += ",deathDate='"+date+"'";
        }else
        {
            query += ",deathDate=NULL";
        }
        res = db.updateDBInfo("dogs",
                              query,
                              "id="+dogId);
        db.closeStatement();
        db =null;
        return res;
    }


    // HEALINGS

    public static ArrayList<Diagnose> getVisitDiagnoses(Visit visit) throws SQLException
    {
        getDiagnoses.setInt(1, visit.getId());
        ResultSet set = getDiagnoses.executeQuery();
            ArrayList<Diagnose> diags = new ArrayList<Diagnose>();
            while(set.next())
            {
                Diagnose diag = new Diagnose(set.getInt(1), //id
                    set.getString(2), //name
                    set.getString(3),
                    visit);
                diag.setSellPrice(set.getDouble(4));
                diag.setDisID(set.getInt(6));
                if(set.getInt(5) == 0)
                    diag.setFinal();
                else
                    diag.setPredv();
                diags.add(diag);
            }
            return diags;
    }
    public static ArrayList<Drug> getVisitDrugs(Visit visit) throws SQLException
    {
        getDrugs.setInt(1, visit.getId());
        ResultSet set = getDrugs.executeQuery();

        ArrayList<Drug> drugs = new ArrayList<Drug>();
        while (set.next()) {
            Drug drug = new Drug(set.getInt("drugId"),
                    set.getInt("distrId"),
                    set.getString("name"),
                    set.getDate("inDate"),
                    visit);
            drug.setDescr(set.getString("descr"));
            drug.setSellPrice(set.getDouble("sell")/set.getDouble("amount"));
            drug.setSellPrices(set.getDouble("sellLow"), set.getDouble("sellMedium"), set.getDouble("sellHigh"));
            drug.setType(set.getString("type"));
            drug.setPayed(set.getDouble("payed"));
            drug.setAmount(set.getDouble("amount"));
            drug.setRecId(set.getInt("skladId"));
            drug.setDisID(set.getInt("disId"));
            drug.setMaxAmount(Dogy.roundTwoDecimals(set.getDouble("amount") + set.getDouble("sklAmount")));
            drugs.add(drug);
        }
        return drugs;
    }

    public static ArrayList<Operation> getVisitOperations(Visit visit) throws SQLException {

        getOperations.setInt(1, visit.getId());
        ResultSet set = getOperations.executeQuery();
        ArrayList<Operation> opers = new ArrayList<Operation>();
        while (set.next()) {
            Operation oper = new Operation(set.getInt(1),
                                           set.getDouble(2),
                                           set.getString(3),
                                           set.getString(4),
                                           visit);
            oper.setPayed(set.getDouble(5));
            oper.setDisID(set.getInt(6));
            opers.add(oper);
        }
        return opers;
    }
    public static ArrayList<HealingSet> getHealing(int dogId) throws SQLException {
        clearStatements();
        getHealings.setInt(1, dogId);
        ResultSet set = getHealings.executeQuery();
        ItemList<Item> visitList;
        ArrayList<HealingSet> healings = new ArrayList<HealingSet>();
        HealingSet visits = null;
        
        while(set.next())
        {
            if( visits == null || visits.getId() != set.getInt("healId"))
            {
              visits = new HealingSet(set.getInt("healId"));
              visits.setName(set.getString("healName"));
              visits.setResult(set.getString("result"));
              visits.setLastVisit(set.getDate("date"));
              healings.add(visits);
            }
            visitList = new ItemList(set.getDate("date"));
            Visit visit = new Visit();
            visit.setId(set.getInt("id"));
            visit.setDescr(set.getString("descr"));
            visit.setSellPrice(set.getDouble("visitPrice"));
            visit.setCurrency(set.getString("currency"));
            visit.setDoctor(set.getInt("profId") != 0 ? 
                new Doctor(set.getInt("profId"),set.getString("name"), set.getString("surname")):null);
            
            visit.setDisDate(set.getDate("date"));
            visit.setRecom(set.getString("recom"));
            visit.setVisitPayed(set.getDouble("visitPricePayed"));
            visit.setVisitOtherPayed(set.getDouble("visitOtherPayed"));
            visit.setOtherPrice(set.getDouble("otherCosts"));
            visit.setStatus(set.getString("status"));
            visit.setBelongsToHealing(visits);
            visit.setCardPayment(set.getBoolean("cardPayment"));
            //visit.setHealing
            visitList.add(visit);
            // gather diags
            visitList.addAll(getVisitDiagnoses(visit));
            // gather drugs
            visitList.addAll(getVisitDrugs(visit));
            // gather oper
            //
            visitList.addAll(getVisitOperations(visit));
            // gather items
            //visitList.addAll(getVisitItems(visit));
            visitList.setBelongsTo(visits);
            visits.addVisit(visitList);

        }
        Collections.sort(healings, new HealingSetComparator());
        return healings;
    }
    public static int unDelete(ArrayList<Integer> deletedDogs) throws SQLException
    {
        Database db = new Database();
        int res=0;
        String query = "id="+deletedDogs.get(0);
        for(int i=1; i < deletedDogs.size(); i++)
            query += " OR id="+deletedDogs.get(i);
        
        res = db.updateDBInfo("dogs",
                              "deleted=0"
                              ,
                               query);
        db.closeStatement();
        db =null;
        return res;
    }

    private static void clearStatements() throws SQLException
    {
        if(getHealings != null)
          getHealings.clearParameters();
        if(getDrugs != null)
          getDrugs.clearParameters();
        if(getDiagnoses != null)
          getDiagnoses.clearParameters();
        if(getOperations != null)
          getOperations.clearParameters();
    }
    // prepare statements
    public static void prepareHealingStatements(Connection con) throws SQLException
    {
        getHealings = con.prepareStatement(
        "SELECT vis.id, vis.date, vis.exec, vis.visitPrice, vis.currency, vis.descr, vis.profId, " +
                "vis.name, vis.surname, H.id AS healId, H.name AS healName, " +
                "H.result, H.lastVisit, vis.recom, " +
                "vis.visitPricePayed, vis.visitOtherPayed, vis.otherCosts, vis.status, vis.cardPayment FROM " +
                "( SELECT V.id, currency, healingId, date, exec, recom, " +
                " visitPrice, visitPricePayed, visitOtherPayed, otherCosts, descr, V.status, cardPayment, " +
                " P.id AS profId, P.name, P.surname FROM " +
                "visits V LEFT JOIN profiles P ON V.exec = P.id WHERE dogId = ?) vis " +
                "JOIN healing H ON vis.healingId = H.id WHERE "+
                " 1=1 ORDER BY H.id ASC, vis.date DESC, vis.id DESC");
        getDrugs = con.prepareStatement(
        "SELECT DR.drugId, DR.distrId, drugs.name, DR.inDate, DR.descr, DR.sell," +
                " drugs.type, DR.payed, DR.amount, DR.sellLow, DR.sellMedium, " +
                " DR.sellHigh, DR.skladId, DR.disId, DR.sklAmount FROM " +
                "(SELECT O.id AS drugId, O.distrId, inDate, disDate, O.descr, " +
                " O.sell, O.payed, O.amount, O.sellLow,O.sellMedium,O.sellHigh, O.skladId, O.disId, O.sklAmount " +
                "FROM " +
                "(SELECT O.id, O.distrId, O.sell as sellLow, O.sellMedium, O.sellHigh, " +
                " O.rec_id as skladId, O.currentAmount as sklAmount, " +
                " DD.sellPrice as sell, DD.descr, DD.payed, DD.amount, O.date AS inDate, DD.date AS disDate, DD.disId " +
                "FROM skladDetailed O RIGHT JOIN discharged_drugs DD ON O.rec_id = DD.drugId " +
                "WHERE DD.deleted = 0 AND DD.visitId = ? ) O ) DR " +
                "LEFT JOIN drugs ON drugs.id = DR.drugId ");
        getDiagnoses = con.prepareStatement(
                "SELECT diagId, name, descr, price,diagStatus, DM.disId FROM "+
                "diagnoses_made DM LEFT JOIN diagnoses D ON DM.diagId = D.id WHERE "+
                "DM.deleted=0 AND visitId = ? ");
        getOperations = con.prepareStatement(
                "SELECT O.id, O.price, O.name, O.descr, O.payed,O.disId FROM " +
                "(SELECT O.id, O.name, OM.price, OM.payed, OM.descr,OM.disId " +
                "FROM operations O RIGHT JOIN operations_made OM ON O.id = OM.operId " +
                "WHERE OM.deleted=0 AND OM.visitId = ? " +
                " ORDER BY OM.date DESC) O ");
    }

}
